package edu.cs.usask.ca.silver.persistence.sql;

/**
 * Collection of SQL strings required to fulfill file related requests. The
 * strings operate the File table as well as InputFiles, AnalysisFiles, and
 * TrajectoryFiles and SC2F (a linking table that connects files to
 * scenarioCollections).
 * 
 * @author Daniel Funk
 * @author Yudi Xue
 */
public interface PgSQLFileStringsI {

	// This string uses "?" because it is used as PreparedStatement rather than
	// String - this is
	// necessary if you want to insert binary data.
	public static final String FILE_INSERT = "INSERT INTO File (f_identity, f_name, f_description, f_oid) "
			+ "VALUES (?, ?, ?, ?)";

	public static final String FILE_SELECT_NAME = "SELECT f_name FROM File WHERE f_identity = '%s'";
	public static final String FILE_SELECT_DESCRIPTION = "SELECT f_description FROM File WHERE f_identity = '%s'";
	public static final String FILE_SELECT_DATA = "SELECT f_oid FROM File WHERE f_identity = '%s'";

	public static final String FILE_SET_NAME = "UPDATE File SET f_name = '%s' WHERE f_identity = '%s'";
	public static final String FILE_SET_DESCRIPTION = "UPDATE File SET f_description = '%s' WHERE f_identity = '%s'";

	// This string uses "?" because it is used as PreparedStatement rather than
	// String - this is
	// necessary if you want to insert binary data.
	public static final String FILE_SET_DATA = "UPDATE File SET f_oid = ? WHERE f_identity = '%s'";

	public static final String FILE_DELETE = "DELETE FROM File WHERE f_identity = '%s'";

	public static final String INPUTFILE_INSERT = "INSERT INTO InputFiles (if_f_identity, if_s_identity) VALUES ('%s', '%s')";
	public static final String ANALYSISFILE_INSERT = "INSERT INTO AnalysisFiles (af_f_identity, af_s_identity) VALUES ('%s', '%s')";
	public static final String TRAJECTORYFILE_INSERT = "INSERT INTO TrajectoryFiles (tf_f_identity, tf_s_identity) VALUES ('%s', '%s')";

	public static final String FILE_ADDTO_SCENARIOCOLLECTION = "INSERT INTO SC2F (sc2f_f_identity, sc2f_sc_identity) VALUES ('%s', '%s')";
	public static final String FILE_ADDTO_SCENARIO = "INSERT INTO S2F (s2f_f_identity, s2f_s_identity) VALUES ('%s', '%s')";
	public static final String FILE_ADDTO_MODELPROJECT = "INSERT INTO MP2F (mp2f_f_identity, mp2f_mp_identity) VALUES ('%s', '%s')";
	public static final String FILE_ADDTO_MODELVERSION = "INSERT INTO MV2F (mv2f_f_identity, mv2f_mv_identity) VALUES ('%s', '%s')";

	public static final String FILE_DELETE_ASSOCIATION_FROM_MODEL_PROJECT = "DELETE FROM File";
	public static final String BACK_UP_DATABASE = "BACKUP TO '%s'";

	// , f_description, f_data FROM File
	// TODO update for PgSQL
	public static final String FILE_DUPLICATE = "INSERT INTO File (f_identity, f_name, f_description, f_data) "
			+ "VALUES ('%s', (SELECT f_name FROM File WHERE f_identity = '%s'), (SELECT f_description FROM File WHERE f_identity = '%s') , (SELECT f_data FROM File WHERE f_identity = '%s'))";

	// public static final String MULTIPLE_FILE_DUPLICATE =
	//
	// String query = "select * from table where RecordId in ({0});";
	// String formatted = String.Format(query, String.Join(",",
	// list.ToArray()));
}
